const moment = require("moment-timezone");
const utils = require("../utils/index.js");

const DEFAULT_CYCLE_DAYS = 7;
const DEFAULT_WINDOW_DAYS = 1;
const DEFAULT_CYCLE_UNIT = 'week';
const pool = require('../pool.js');
const poolmomo = require('../poolmomo.js');

// 按包名选库
function getPool(pk) {
    return pk === 'com.trans.translationAiAndSubtitle' ? poolmomo : pool;
}

async function mysqlQuery(pk, sql, arr = []) {
    return new Promise((resolve, reject) => {
        getPool(pk).query(sql, arr, (error, results) => {
            if (error) return reject(error);
            resolve(results);
        });
    });
}

/**
 * 实时查询产品周期配置
 */
async function getProductCycleConfig(pk, productId) {
    const sql = `
        SELECT product_id, pk, cycle_days, cycle_unit, window_days, trial_days, remark
        FROM js_app_product_cycle_config
        WHERE product_id = ? AND (pk = ? OR pk IS NULL)
        ORDER BY pk DESC LIMIT 1
    `;
    const result = await mysqlQuery(pk, sql, [productId, pk]);
    if (!result.length) return {
        cycle_days: DEFAULT_CYCLE_DAYS,
        cycle_unit: DEFAULT_CYCLE_UNIT,
        window_days: DEFAULT_WINDOW_DAYS,
        trial_days: 0
    };
    return result[0];
}

/**
 * 生成cohort统计表唯一id
 */
function getCohortStatId(pk, productId, cohortDate, termN) {
    return utils.createId();
}

/**
 * 获取产品实际周期天数
 */
function getActualCycleDays(prodCfg) {
    const { cycle_unit, cycle_days } = prodCfg;

    switch (cycle_unit) {
        case 'day':
            return cycle_days || 1;
        case 'week':
            return cycle_days || 7;
        case 'month':
            return 30;
        case 'quarter':
            return 90;
        case 'year':
            return 365;
        default:
            return cycle_days || DEFAULT_CYCLE_DAYS;
    }
}

/**
 * 计算付费周期的续订窗口
 * @param {Object} prodCfg 产品配置
 * @param {string} cohortDate 首购日期
 * @param {number} termN 第几期付费（1=第一次付费，2=第二次付费...）
 * @param {boolean} hasTrial 是否有试用期
 * @returns {Object} {windowStart, windowEnd}
 */
function getRenewWindow(prodCfg, cohortDate, termN, hasTrial = false) {
    const { cycle_unit, window_days, trial_days = 0 } = prodCfg;
    const m = moment(cohortDate, "YYYY-MM-DD");
    let windowStart, windowEnd;

    // 计算第一次付费的基准日期
    const firstPaymentDate = hasTrial ?
        m.clone().add(trial_days, 'days') :  // 有试用期：从试用期结束开始
        m.clone();                           // 无试用期：从首购日开始

    if (cycle_unit === 'day' || cycle_unit === 'week') {
        const cycleDays = getActualCycleDays(prodCfg);
        // 第N期付费窗口 = 第一次付费日期 + (N-1) * 周期天数
        windowStart = firstPaymentDate.clone()
            .add((termN - 1) * cycleDays - window_days, 'days')
            .startOf('day');
        windowEnd = firstPaymentDate.clone()
            .add((termN - 1) * cycleDays + window_days, 'days')
            .endOf('day');
    } else if (cycle_unit === 'month') {
        windowStart = firstPaymentDate.clone()
            .add(termN - 1, 'months')
            .subtract(window_days, 'days')
            .startOf('day');
        windowEnd = firstPaymentDate.clone()
            .add(termN - 1, 'months')
            .add(window_days, 'days')
            .endOf('day');
    } else if (cycle_unit === 'quarter') {
        windowStart = firstPaymentDate.clone()
            .add((termN - 1) * 3, 'months')
            .subtract(window_days, 'days')
            .startOf('day');
        windowEnd = firstPaymentDate.clone()
            .add((termN - 1) * 3, 'months')
            .add(window_days, 'days')
            .endOf('day');
    } else if (cycle_unit === 'year') {
        windowStart = firstPaymentDate.clone()
            .add(termN - 1, 'years')
            .subtract(window_days, 'days')
            .startOf('day');
        windowEnd = firstPaymentDate.clone()
            .add(termN - 1, 'years')
            .add(window_days, 'days')
            .endOf('day');
    } else {
        // 兜底逻辑
        const cycleDays = getActualCycleDays(prodCfg);
        windowStart = firstPaymentDate.clone()
            .add((termN - 1) * cycleDays - window_days, 'days')
            .startOf('day');
        windowEnd = firstPaymentDate.clone()
            .add((termN - 1) * cycleDays + window_days, 'days')
            .endOf('day');
    }

    return { windowStart, windowEnd };
}

/**
 * 获取cohort成员详细信息，按用户类型分类
 */
async function getCohortMembersDetail(pk, productId, startUTC, endUTC) {
    const cohortMembers = await mysqlQuery(
        pk,
        `SELECT DISTINCT 
            original_transaction_id, 
            offer_discount_type,
            signed_date,
            CASE 
                WHEN offer_discount_type IN ('FREE_TRIAL') THEN 'trial'
                WHEN offer_discount_type = 'PAY_AS_YOU_GO' THEN 'promo'
                ELSE 'normal'
            END as user_type
         FROM js_app_apple_notifications
         WHERE pk=? AND product_id=? 
           AND signed_date >= ? AND signed_date <= ?
           AND environment = 'Production' AND subtype = 'INITIAL_BUY' AND status = '1'`,
        [pk, productId, startUTC, endUTC]
    );

    // 按用户类型分组
    const trialUsers = cohortMembers.filter(r => r.user_type === 'trial');
    const promoUsers = cohortMembers.filter(r => r.user_type === 'promo');
    const normalUsers = cohortMembers.filter(r => r.user_type === 'normal');

    return {
        trialUsers,
        promoUsers,
        normalUsers,
        totalBase: cohortMembers.length,
        trialBase: trialUsers.length,
        promoBase: promoUsers.length,
        normalBase: normalUsers.length
    };
}

/**
 * 统计指定用户组在指定窗口期的续订情况
 */
async function countRenewals(pk, productId, userTxIds, windowStart, windowEnd) {
    if (userTxIds.length === 0) return 0;

    const startTime = windowStart.format("YYYY-MM-DD HH:mm:ss");
    const endTime = windowEnd.format("YYYY-MM-DD HH:mm:ss");

    const renewResult = await mysqlQuery(
        pk,
        `SELECT COUNT(DISTINCT n.original_transaction_id) as cnt
         FROM js_app_apple_notifications n
         WHERE n.pk = ?
           AND n.product_id = ?
           AND n.environment = 'Production'
           AND (n.notification_type = 'DID_RENEW' OR n.notification_type='SUBSCRIBED')
           AND n.status = '1'
           AND n.original_transaction_id IN (${userTxIds.map(() => '?').join(',')})
           AND n.signed_date >= ?
           AND n.signed_date <= ?`,
        [pk, productId, ...userTxIds, startTime, endTime]
    );

    return renewResult[0]?.cnt || 0;
}

/**
 * cohort统计&历史补全主服务
 */
class SubscriptionCohortStatsService {
    /**
     * 保存/更新产品配置
     */
    async upsertProductConfig(config) {
        const { product_id, pk, cycle_days, cycle_unit, window_days, trial_days, remark } = config;

        const sql = `
            INSERT INTO js_app_product_cycle_config 
            (product_id, pk, cycle_days, cycle_unit, window_days, trial_days, remark)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
                cycle_days = VALUES(cycle_days),
                cycle_unit = VALUES(cycle_unit),
                window_days = VALUES(window_days),
                trial_days = VALUES(trial_days),
                remark = VALUES(remark),
                update_date = CURRENT_TIMESTAMP
        `;

        return await mysqlQuery(pk, sql, [product_id, pk, cycle_days, cycle_unit, window_days, trial_days, remark || '']);
    }

    /**
     * 获取所有产品配置
     */
    async getAllProductConfigs() {
        // 从所有可能的数据库中获取配置
        const allConfigs = [];

        // 这里需要根据你的实际情况调整，获取所有可能的pk
        const possiblePks = [
            'com.seamobitech.whatsscanwebclone',
            'com.trans.translationAiAndSubtitle'
        ];

        for (const pk of possiblePks) {
            try {
                const configs = await mysqlQuery(pk, `
                    SELECT product_id, pk, cycle_days, cycle_unit, window_days, trial_days, remark, 
                           create_date, update_date
                    FROM js_app_product_cycle_config 
                    WHERE pk = ?
                    ORDER BY update_date DESC
                `, [pk]);

                allConfigs.push(...configs);
            } catch (error) {
                console.log(`获取 ${pk} 的配置时出错:`, error.message);
            }
        }

        return allConfigs;
    }

    /**
     * 删除产品配置
     */
    async deleteProductConfig(productId, pk) {
        const sql = `DELETE FROM js_app_product_cycle_config WHERE product_id = ? AND pk = ?`;
        return await mysqlQuery(pk, sql, [productId, pk]);
    }

    /**
     * 单个产品历史数据补全
     */
    async repairSingleProduct(pk, productId, maxTerms = 6) {
        console.log(`开始单个产品历史补全: ${pk}#${productId}`);

        // 新增：获取今天日期（东八区）
        const todayStr = moment().tz('Asia/Shanghai').format('YYYY-MM-DD');

        // 查询所有 cohort 日期
        const res = await mysqlQuery(pk,
            `SELECT DISTINCT DATE(signed_date) as cohort_date
             FROM js_app_apple_notifications 
             WHERE pk=? AND product_id=? AND environment='Production' AND subtype='INITIAL_BUY' AND status='1'
             ORDER BY cohort_date`,
            [pk, productId]
        );
        const cohortDates = res.map(r => r.cohort_date);
        console.log(`${pk}#${productId} 共有 ${cohortDates.length} 个cohort日期`);

        for (const cohortDate of cohortDates) {
            // 修正：处理 cohortDate 格式，变成年月日字符串
            const cohortDateStr = (typeof cohortDate === 'string')
                ? cohortDate.slice(0, 10)
                : moment(cohortDate).format('YYYY-MM-DD');

            // 新增：判断是否为今天或以后，避免处理未完整的数据
            if (cohortDateStr >= todayStr) {
                console.log(`[${pk}#${productId}] 跳过cohort日期 ${cohortDateStr} (今天及以后)`);
                continue;
            }

            // 正常处理
            await this.calcCohortByDay(pk, productId, cohortDate, maxTerms);
        }

        console.log(`单个产品历史补全完成: ${pk}#${productId}`);
    }
    

    /**
     * 获取趋势分析数据
     */
    async getTrendAnalysis(pk, productId, days = 30) {
        const endDate = moment().subtract(1, 'day').format('YYYY-MM-DD');
        const startDate = moment().subtract(days + 1, 'days').format('YYYY-MM-DD');

        const sql = `
            SELECT 
                cohort_date,
                term_n,
                user_type,
                user_base,
                renew_count,
                ROUND(renew_rate * 100, 2) as renew_rate_pct
            FROM js_app_subscription_cohort_stats
            WHERE pk = ? AND product_id = ?
              AND cohort_date >= ? AND cohort_date <= ?
            ORDER BY cohort_date DESC, user_type, term_n
        `;

        const rawData = await mysqlQuery(pk, sql, [pk, productId, startDate, endDate]);

        // 数据处理和分析
        const analysis = {
            summary: {
                total_cohorts: new Set(rawData.map(r => r.cohort_date)).size,
                date_range: { start_date: startDate, end_date: endDate },
                avg_renewal_rates: {}
            },
            cohort_details: {},
            user_type_comparison: {
                trial: { avg_rate: 0, cohort_count: 0 },
                promo: { avg_rate: 0, cohort_count: 0 },
                normal: { avg_rate: 0, cohort_count: 0 },
                total: { avg_rate: 0, cohort_count: 0 }
            }
        };

        // 按cohort_date分组
        rawData.forEach(row => {
            const key = row.cohort_date;
            if (!analysis.cohort_details[key]) {
                analysis.cohort_details[key] = {};
            }
            if (!analysis.cohort_details[key][row.user_type]) {
                analysis.cohort_details[key][row.user_type] = [];
            }
            analysis.cohort_details[key][row.user_type].push(row);
        });

        // 计算用户类型平均续订率
        const userTypeStats = {};
        rawData.forEach(row => {
            if (!userTypeStats[row.user_type]) {
                userTypeStats[row.user_type] = { total_rate: 0, count: 0 };
            }
            userTypeStats[row.user_type].total_rate += row.renew_rate_pct;
            userTypeStats[row.user_type].count++;
        });

        Object.keys(userTypeStats).forEach(userType => {
            const stats = userTypeStats[userType];
            analysis.user_type_comparison[userType] = {
                avg_rate: (stats.total_rate / stats.count).toFixed(2),
                cohort_count: stats.count
            };
        });

        return analysis;
    }

    /**
     * 统计指定cohort日期、产品ID每期续订率
     * 按用户类型分别统计：试用用户、首购优惠用户、正常用户
     */
    async calcCohortByDay(pk, productId, cohortDate, maxTerms = 6) {
        // 修正：转为标准字符串
        let cohortDateStr;
        if (cohortDate instanceof Date) {
            cohortDateStr = moment(cohortDate).format("YYYY-MM-DD");
        } else if (typeof cohortDate === 'string') {
            cohortDateStr = cohortDate.slice(0, 10); // 防止有时分秒
        } else {
            cohortDateStr = String(cohortDate);
        }

        const startBJ = `${cohortDateStr} 00:00:00`;
        const endBJ = `${cohortDateStr} 23:59:59`;
        const startTime = startBJ;
        const endTime = endBJ;

        // 获取详细的cohort成员信息
        const cohortInfo = await getCohortMembersDetail(pk, productId, startTime, endTime);
        const { trialUsers, promoUsers, normalUsers, totalBase, trialBase, promoBase, normalBase } = cohortInfo;

        if (totalBase === 0) {
            console.log(`[Cohort] ${pk}#${productId}#${cohortDateStr}: 无首购用户，跳过`);
            return;
        }

        // 获取产品周期参数
        const prodCfg = await getProductCycleConfig(pk, productId);

        console.log(`[Cohort] ${pk}#${productId}#${cohortDateStr}: 总基数=${totalBase} (试用=${trialBase}, 首购优惠=${promoBase}, 正常=${normalBase})`);
        console.log(`[Cohort] 产品配置: ${prodCfg.cycle_unit}(${getActualCycleDays(prodCfg)}天), 试用期=${prodCfg.trial_days || 0}天, 窗口=${prodCfg.window_days}天`);

        // 分别统计三种用户类型的续订情况
        // await this.calcUserTypeRenewals(pk, productId, cohortDateStr, maxTerms, prodCfg, 'trial', trialUsers, trialBase, true);
        // await this.calcUserTypeRenewals(pk, productId, cohortDateStr, maxTerms, prodCfg, 'promo', promoUsers, promoBase, true); // 首购优惠也算有试用
        // await this.calcUserTypeRenewals(pk, productId, cohortDateStr, maxTerms, prodCfg, 'normal', normalUsers, normalBase, false);

        // 汇总统计
        await this.calcUserTypeRenewals(pk, productId, cohortDateStr, maxTerms, prodCfg, 'total',
            [...trialUsers, ...promoUsers, ...normalUsers], totalBase, trialBase > 0 || promoBase > 0);
    }

    /**
     * 统计特定用户类型的续订情况
     */
    async calcUserTypeRenewals(pk, productId, cohortDate, maxTerms, prodCfg, userType, users, userBase, hasTrial) {
        if (userBase === 0) return;

        const userTxIds = users.map(u => u.original_transaction_id);

        for (let termN = 1; termN <= maxTerms; termN++) {
            const { windowStart, windowEnd } = getRenewWindow(prodCfg, cohortDate, termN, hasTrial);

         

            // 如果窗口尚未结束，不统计
            if (windowEnd.isAfter(moment())) {
                // 不写库，也不计入日志，可以根据你需求，只 log 下
                console.log(`[${userType.toUpperCase()}] 第${termN}期: 窗口期未到(${windowEnd.format('MM-DD')}), 跳过`);
                break;
            }

            // 窗口正常已结束，统计续订
            const renewCount = await countRenewals(pk, productId, userTxIds, windowStart, windowEnd);
            const renewRate = userBase > 0 ? (renewCount / userBase) : 0;

            await this.upsertCohortStats({
                pk, productId, cohortDate, termN, userType,
                userBase, renewCount, renewRate,
                windowStart: windowStart.format('YYYY-MM-DD HH:mm:ss'),
                windowEnd: windowEnd.format('YYYY-MM-DD HH:mm:ss')
            });

            console.log(`[${userType.toUpperCase()}] 第${termN}期: 续订=${renewCount}/${userBase}(${(renewRate * 100).toFixed(1)}%) 窗口=${windowStart.format('MM-DD')}~${windowEnd.format('MM-DD')}`);
        }
    }

    /**
     * INSERT/UPDATE cohort汇总表
     */
    async upsertCohortStats(stats) {
        const id = getCohortStatId(stats.pk, stats.productId, stats.cohortDate, `${stats.termN}_${stats.userType}`);
        await mysqlQuery(
            stats.pk,
            `INSERT INTO js_app_subscription_cohort_stats
            (id, pk, product_id, cohort_date, term_n, user_type,
             user_base, renew_count, renew_rate, window_start, window_end)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
              user_base=VALUES(user_base),
              renew_count=VALUES(renew_count),
              renew_rate=VALUES(renew_rate),
              window_start=VALUES(window_start),
              window_end=VALUES(window_end),
              update_date=CURRENT_TIMESTAMP`,
            [
                id, stats.pk, stats.productId, stats.cohortDate, stats.termN, stats.userType,
                stats.userBase, stats.renewCount, stats.renewRate, stats.windowStart, stats.windowEnd
            ]
        );
    }

    /**
     * 全量历史修复
     */
    async repairAllHistory(pkArr, maxTerms = 6) {
        console.log(`开始全量历史修复，包数量: ${pkArr.length}, 最大期数: ${maxTerms}`);

        for (const pk of pkArr) {
            console.log(`处理包: ${pk}`);

            const res = await mysqlQuery(
                pk,
                `SELECT DISTINCT DATE(signed_date) as cohort_date
                 FROM js_app_apple_notifications 
                 WHERE pk=? AND environment='Production' AND subtype='INITIAL_BUY' AND status='1'
                 ORDER BY cohort_date`,
                [pk]
            );
            const allCohortDates = res.map(r => r.cohort_date);
            console.log(`${pk} 共有 ${allCohortDates.length} 个cohort日期`);

            for (const cohortDate of allCohortDates) {
                const prods = await mysqlQuery(
                    pk,
                    `SELECT DISTINCT product_id 
                     FROM js_app_apple_notifications
                     WHERE pk=? AND environment='Production' AND subtype='INITIAL_BUY' AND status='1'
                       AND signed_date >= ? AND signed_date <= ?`,
                    [
                        pk,
                        moment.tz(cohortDate + " 00:00:00"),
                        moment.tz(cohortDate + " 23:59:59")
                    ]
                );

                for (const { product_id: productId } of prods) {
                    await this.calcCohortByDay(pk, productId, cohortDate, maxTerms);
                }
            }
        }
        console.log("全量历史修复完成！");
    }

    /**
     * 每天自动任务
     */
    async dailyRepairYesterdayAllCohort(pkArr, maxTerms = 6) {
        const today = moment.tz("Asia/Shanghai").startOf('day');
        const yesterday = today.clone().subtract(1, 'day');

        console.log(`开始每日任务，截止日期: ${yesterday.format('YYYY-MM-DD')}`);

        for (const pk of pkArr) {
            const res = await mysqlQuery(
                pk,
                `SELECT DISTINCT DATE(signed_date) as cohort_date
                 FROM js_app_apple_notifications 
                 WHERE pk=? AND environment='Production' AND subtype='INITIAL_BUY' AND status='1' 
                   AND signed_date < ?
                 ORDER BY cohort_date`,
                [pk, today.format("YYYY-MM-DD HH:mm:ss")]
            );
            const allCohortDates = res.map(r => r.cohort_date);

            for (const cohortDate of allCohortDates) {
                const prods = await mysqlQuery(
                    pk,
                    `SELECT DISTINCT product_id 
                     FROM js_app_apple_notifications
                     WHERE pk=? AND environment='Production' AND subtype='INITIAL_BUY' AND status='1'
                       AND signed_date >= ? AND signed_date <= ?`,
                    [
                        pk,
                        moment.tz(cohortDate + " 00:00:00"),
                        moment.tz(cohortDate + " 23:59:59")
                    ]
                );

                for (const { product_id: productId } of prods) {
                    const prodCfg = await getProductCycleConfig(pk, productId);

                    // 检查哪些期数的窗口期已过
                    for (let termN = 1; termN <= maxTerms; termN++) {
                        const { windowEnd: trialWindowEnd } = getRenewWindow(prodCfg, cohortDate, termN, true);
                        const { windowEnd: normalWindowEnd } = getRenewWindow(prodCfg, cohortDate, termN, false);

                        // 如果任一类型用户的窗口期已过，就重新统计整个cohort
                        if (trialWindowEnd.isSameOrBefore(yesterday, 'day') ||
                            normalWindowEnd.isSameOrBefore(yesterday, 'day')) {
                            await this.calcCohortByDay(pk, productId, cohortDate, maxTerms);
                            break; // 避免重复统计同一个cohort
                        }
                    }
                }
            }
        }
        console.log("每日修复任务完成！");
    }

    /**
     * 生成cohort报告（按用户类型展示）
     */
    async getCohortReport(pk, productId, startDate, endDate) {
        const sql = `
            SELECT 
                cohort_date,
                term_n,
                user_type,
                user_base,
                renew_count,
                ROUND(renew_rate * 100, 2) as renew_rate_pct,
                window_start,
                window_end
            FROM js_app_subscription_cohort_stats
            WHERE pk = ? AND product_id = ?
              AND cohort_date >= ? AND cohort_date <= ?
            ORDER BY cohort_date DESC, user_type, term_n
        `;

        return await mysqlQuery(pk, sql, [pk, productId, startDate, endDate]);
    }
}

module.exports = SubscriptionCohortStatsService;